package com.example.goodsmanagementcabinet.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

import com.blankj.utilcode.utils.SDCardUtils;

import java.io.File;

public class DatabaseHelper extends SQLiteOpenHelper {
    public static File KeyBoxDB = new File(SDCardUtils.getSDCardPath(), "GOODBOX_DB");
    private static final String DB_NAME = KeyBoxDB + "/" + "GoodBox.db"; //数据库名称
    private static final int VERSION = 2; //数据库版本
    private static SQLiteOpenHelper mInstance;

    public synchronized static SQLiteOpenHelper getInstance(Context context) {
        if (mInstance == null) {
            mInstance = new DatabaseHelper(context, DB_NAME, null, VERSION);
        }
        return mInstance;
    }

    /**
     * @param context 上下文
     * @param name
     * @param factory 数据库名
     *                可选的数据库游标工厂类，当查询(query)被提交时，该对象会被调用来实例化一个游标。默认为null。
     * @param version 数据库版本号
     */
    public DatabaseHelper(Context context, String name, CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {// 覆写onCreate方法，当数据库创建时就用SQL命令创建一个表
        String sql;

        /**
         * 钥匙柜信息表
         * "cabinetId": "1",
         * "cabinetName": "稠城所12号柜",
         * "cabinetCode": null,
         * "token": "WOSCIRRE",
         * "cabinetAddress": null,
         * "status": "0",
         * "cdate": "2021-04-27 15:08:20",
         * "uuser": null,
         * "udate": null,
         * "remoteId": "1",
         * "cusername": null,
         * "cabinetDeptName": null
         */
        sql = "create table keyboxs(_id integer primary key autoincrement," +
                "cabinetId text," +                 //钥匙柜id
                "cabinetName text," +        //钥匙柜名称
                "cabinetCode text," +
                "token text," +
                "cabinetAddress text," +
                "status text," +
                "cdate text," +
                "uuser text," +
                "udate text," +
                "remoteId text," +
                "deptId text," +
                "cusername text," +
                "deptName text," +
                "constraint \"不重复规则\" unique (\"cabinetId\") on conflict replace);";
        db.execSQL(sql);


        /**
         *  用户信息表
         * "deptName": "测试",
         * "nickname": "测试001",
         * "deptId": "1",
         * "faceList": null,
         * "userId": "2",
         * "username": "000015"
         */
        sql = "create table users(_id integer primary key autoincrement," +
                "deptName text," +                 //人员编号
                "nickname  text," +
                "deptId  text," +                  // 姓名
                "userId  text," +                  // 男、女
                "username text," +
                "image text," +
                "constraint \"不重复规则\" unique (\"userId\") on conflict replace);";
        db.execSQL(sql);


        /**
         *  钥匙信息表
         "carId": "1",
         "carName": "特警大队1号车",
         "carCode": "浙A9999",
         "remoteId": "1",
         "cabinetid": "",
         "cabinetName": null,
         "status": "-1",
         "location": null,
         "mcode": null,
         "adminUser": "2",
         "deptid": "1",
         "deptName": "测试",
         "cdate": "2021-04-27 15:09:20",
         "udate": null
         */
        sql = "create table keys(id integer primary key autoincrement," +
                "keyId text," +
                "keyName  text," +
                "keyCode  text," +
                "status text," +
                "location text," +
                "constraint \"不重复规则\" unique (\"keyCode\") on conflict replace);";
        db.execSQL(sql);

        /**指纹表
         * "id": "151",
         * "userid": "2",
         * "type": "zsxmz",
         * "finger": "1w23232232322ewewe232",
         * "typeName": null,
         * "result": null,
         * "roleid": null,
         * "username": null,
         * "yz": null
         * */
        sql = "create table fingers(_id integer primary key autoincrement," +
                "userid  text," +                // 人员编号
                "type  text," +           // 0：左手小指  1：左手无名指 2：左手中指 3：左手食指 4：左手大拇指5: 右手小指  6：右手无名指 7：右手中指  8：右手食指 9：右手大拇指
                "finger  text," +            //  指纹特征
                "constraint \"不重复规则\" unique (\"_id\") on conflict replace);";
        db.execSQL(sql);

        /**
         * "id": "1",
         * "type": null,
         * "userId": "222311",
         * "userName": "王宏宇",
         * "carId": "123459",
         * "carName": "特警大队4号车-浙A9809",
         * "startTime": "2021-05-10 09:30:00",
         * "endTime": "2021-05-10 19:00:00",
         * "code": "SQ01929",
         * "status": "-1"
         * */
        sql = "create table tasks(_id integer primary key autoincrement," +
                "id  text," +
                "type  text," +
                "userId  text," +
                "userName  text," +
                "carId  text," +
                "carName  text," +
                "startTime  text," +
                "endTime  text," +
                "code  text," +
                "status  text," +
                "constraint \"不重复规则\" unique (\"_id\") on conflict replace);";
        db.execSQL(sql);


        /**
         *  钥匙信息表
         */
        sql = "create table keyusers(_id integer primary key autoincrement," +
                "keyid text," +
                "userid  text," +
                "constraint \"不重复规则\" unique (\"_id\") on conflict replace);";
        db.execSQL(sql);

        /**
         * 交接记录表
         */
        sql = "create table uploadrecords( id integer primary key autoincrement," +
                "apiName text," +
                "date  text," +
                "carId  text," +
                "type  text," +
                "cabinetId  text," +
                "userId  text," +
                "code  text," +
                "mcode  text," +
                "applyId  text," +
                "location  text," +
                "status text);";
        db.execSQL(sql);

        /**
         * 物品交接表
         */
        sql = "create table handoverandtakeover( id integer primary key autoincrement," +
                "overid text," +
                "date  text," +
                "handoveruserid  text," +
                "takeoveruserid  text," +
                "overkeyid  text," +
                "status text);";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        try {
            String sql = "DROP TABLE keyboxs";
            db.execSQL(sql);
            sql = "DROP TABLE users";
            db.execSQL(sql);
            sql = "DROP TABLE keys";
            db.execSQL(sql);
            sql = "DROP TABLE fingers";
            db.execSQL(sql);
            sql = "DROP TABLE tasks";
            db.execSQL(sql);
            sql = "DROP TABLE keyusers";
            db.execSQL(sql);
            sql = "DROP TABLE uploadrecords";
            db.execSQL(sql);
        } catch (Exception e) {
        }
        onCreate(db);
    }
}
